<?php
/**
 * Copyright (c) 2010, Slawomir Mazur <mazursllawek@gmail.com>
 * 
 * All rights reserved.
 * 
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions 
 * are met:
 * 
 * o Redistributions of source code must retain the above copyright notice, 
 *   this list of conditions and the following disclaimer.
 * o Redistributions in binary form must reproduce the above copyright notice,
 *   this list of conditions and the following disclaimer in the documentation
 *   and/or other materials provided with the distribution.
 * o Neither the name of the Softisland nor the names of its contributors
 *   may be used to endorse or promote products derived from this software
 *   without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS 
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT 
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR 
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT 
 * HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, 
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT 
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, 
 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY 
 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT 
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE 
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */
/**
 * Enter description here...
 *
 * @package     File
 * @copyright   Slawomir Mazur (c) 2010 (http://www.zend.com/zce.php?c=ZEND011643)
 * @license     http://www.opensource.org/licenses/bsd-license.php
 * @author      Slawomir Mazur <mazursllawek@gmail.com>
 */

class Gp_File_Csv extends Gp_File_Abstract
{
    protected $_lineLength= 0;
    protected $_delimiter = ',';
    protected $_enclosure = '"';
    
    public function __construct($id) 
    {
        parent::__construct($id);
    }
    
    /**
     * Set max file line length
     *
     * @param   int $length
     * @return  Gp_File_Csv
     */
    public function setLineLength($length)
    {
        $this->_lineLength = $length;
        return $this;
    }
    
    /**
     * Set CSV column delimiter
     *
     * @param   string $delimiter
     * @return  Gp_File_Csv
     */
    public function setDelimiter($delimiter)
    {
    	if (116 == ord($delimiter)) {
    		$delimiter = "\t";
    	}
        $this->_delimiter = $delimiter;
        return $this;
    }
    
    /**
     * Set CSV column value enclosure
     *
     * @param   string $enclosure
     * @return  Gp_File_Csv
     */
    public function setEnclosure($enclosure)
    {
        $this->_enclosure = $enclosure;
        return $this;
    }
    
    /**
     * Retrieve CSV file data as array
     *
     * @return  array
     */
    public function getData()
    {
    	$this->_initFileSpecs();
    	
        $data = array();
        
        $fh = fopen($this->_fileName, 'r');
        while ($rowData = fgetcsv($fh, $this->_lineLength, $this->_delimiter, $this->_enclosure)) {
            $data[] = $rowData;
        }
        fclose($fh);
        return $data;
    }
    
    public function parse()
    {
    	$this->_initFileSpecs();
    	
    	// column filtering
		$columns = range(0, count($this->getDocumentColumnNames()) - 1);
		
		if (false !== $this->_columns) {
			$columns = $this->_columns;
		}
		
		$rows = array();
    	
    	/* @var $historyModel System_Model_History */
		$historyModel = Gp_App::getModel('system/history');
		
		/* @var $attributeModel System_Model_Attribute */
		$attributeModel = Gp_App::getModel('system/attribute');
		$mapping = $attributeModel->getAttributesMapping();

		// build insert header
		$columnsHeader = 'library_id, ';
		foreach ($this->_columnsMap['db'] as $index => $attributeId) {
			$columnsHeader .= $mapping[$attributeId]['code'] . ', ';
		}
		
		$startRow = (((int) $this->_startRow) >= 2)? $this->_startRow : 2;
		$endRow = ($this->_endRow)? $this->_endRow : PHP_INT_MAX;
		
		$queryStart = 'INSERT INTO '.$historyModel->getName().' (' .
               substr($columnsHeader, 0, strrpos($columnsHeader, ', ')) .') VALUES ';
        $queryEnd = '';
        
        $write = $historyModel->getAdapter();
        $write->beginTransaction();
    	
        $fh = fopen($this->_fileName, 'r');
        
        try {
	        // default or rows boundary filtering
			if (($this->_loadWhole) || (false !== $this->_startRow) || (false !== $this->_endRow)) {
				
				$i = 1;
				while (true) {
					if ($i < $startRow) {
						fgetcsv($fh, 0, $this->_delimiter, $this->_enclosure);
						$i++;
						continue;
					}
					
					$row = fgetcsv($fh, $this->_lineLength, $this->_delimiter, $this->_enclosure);
					
					// error or end of file
					if (false === $row) {
						break;
					}
					
					$inputValues = $this->_fileInstance->library_id . ', ';

					// read columns and create data for row
					foreach ($columns as $colIndex => $col) {
						$dataType = $mapping[$this->_columnsMap['db'][$colIndex]]['type'];
						$valueInCell = $row[$col];
						
						switch ($dataType) {
							// tinyint, smallint, int
							case (false !== strpos($dataType, 'int')) :
								$inputValues .= (empty($valueInCell))? '0, ' : ((int) $valueInCell) . ', ';
								break;
								
							// text
							case ('text' == $dataType) :
								$inputValues .= (empty($valueInCell)) ?
										'NULL, ' : '"' . addslashes(trim($valueInCell)) . '", ';
								break;
							
							// yes, no enum
							case ('yes/no' == $dataType) :
								$inputValues .= (empty($valueInCell) || (0 == $valueInCell)) ?
										'"no", ' : '"yes", ';
								break;
								
							// char, varchar
							default:
								$inputValues .= (empty($valueInCell)) ?
										'NULL, ' : '"' . trim($valueInCell) . '", ';
								break;
						}
					}
					$rows[] = '(' . substr($inputValues, 0, strrpos($inputValues, ', ')) . ')';
					
					if (sizeof($rows) == 10) {
						$sql = $queryStart.join(',', $rows).$queryEnd;
						$write->query($sql);
						$rows = array();
					}
					
					if ($i++ > $endRow) {
						break;
					}
				}
				if (!empty($rows)) {
	                $sql = $queryStart.join(',', $rows).$queryEnd;
	                $write->query($sql);
	            }
			}
			
			// read only rows
			if (false !== $this->_readRows) {
				
				$i = 1;
				foreach ($this->_readRows as $_row) {
					if (!isset($this->_readRows[$i])) {
						fgetcsv($fh, $this->_lineLength, $this->_delimiter, $this->_enclosure);
						$i++;
						continue;
					}
					
					$row = fgetcsv($fh, $this->_lineLength, $this->_delimiter, $this->_enclosure);
					
					// error or end of file
					if (false === $row) {
						break;
					}
					
					$inputValues = $this->_fileInstance->library_id . ', ';
										
					// read columns and create data for row
					foreach ($columns as $colIndex => $col) {
						$dataType = $mapping[$this->_columnsMap['db'][$colIndex]]['type'];
						$valueInCell = $row[$col];
						
						switch ($dataType) {
							// tinyint, smallint, int
							case (false !== strpos($dataType, 'int')) :
								$inputValues .= (empty($valueInCell))? '0, ' : ((int) $valueInCell) . ', ';
								break;
								
							// text
							case ('text' == $dataType) :
								$inputValues .= (empty($valueInCell)) ?
										'NULL, ' : '"' . addslashes(trim($valueInCell)) . '", ';
								break;
								
							// char, varchar
							default:
								$inputValues .= (empty($valueInCell)) ?
										'NULL, ' : '"' . trim($valueInCell) . '", ';
								break;
						}
					}
					$rows[] = '(' . substr($inputValues, 0, strrpos($inputValues, ', ')) . ')';
					
					if (sizeof($rows) == 50) {
						$sql = $queryStart.join(',', $rows).$queryEnd;
						$write->query($sql);
						$rows = array();
					}					
					$i++;
				}
				if (!empty($rows)) {
	                $sql = $queryStart.join(',', $rows).$queryEnd;
	                $write->query($sql);
	            }
			}
			
			// omit certain rows
			if (false !== $this->_omitRows) {

				$i = 1;
				while (true) {
					if (isset($this->_omitRows[$i])) {
						fgetcsv($fh, 0, $this->_delimiter, $this->_enclosure);
						$i++;
						continue;
					}
					
					$row = fgetcsv($fh, $this->_lineLength, $this->_delimiter, $this->_enclosure);
					
					// error or end of file
					if (false === $row) {
						break;
					}
					
					$inputValues = $this->_fileInstance->library_id . ', ';
										
					// read columns and create data for row
					foreach ($columns as $colIndex => $col) {
						$dataType = $mapping[$this->_columnsMap['db'][$colIndex]]['type'];
						$valueInCell = $row[$col];
						
						switch ($dataType) {
							// tinyint, smallint, int
							case (false !== strpos($dataType, 'int')) :
								$inputValues .= (empty($valueInCell))? '0, ' : ((int) $valueInCell) . ', ';
								break;
								
							// text
							case ('text' == $dataType) :
								$inputValues .= (empty($valueInCell)) ?
										'NULL, ' : '"' . addslashes(trim($valueInCell)) . '", ';
								break;
								
							// char, varchar
							default:
								$inputValues .= (empty($valueInCell)) ?
										'NULL, ' : '"' . trim($valueInCell) . '", ';
								break;
						}
					}
					$rows[] = '(' . substr($inputValues, 0, strrpos($inputValues, ', ')) . ')';
					
					if (sizeof($rows) == 50) {
						$sql = $queryStart.join(',', $rows).$queryEnd;
						$write->query($sql);
						$rows = array();
					}
					$i++;
				}
				if (!empty($rows)) {
	                $sql = $queryStart.join(',', $rows).$queryEnd;
	                $write->query($sql);
	            }
			}
        } catch (Exception $e) {
        	$write->rollback();
        	fclose($fh);
            throw $e;
        }
        
        $write->commit();
        fclose($fh);
            
		return $this;
    }
    
	public function setRowsToRead($rows = null)
	{
		if (!empty($rows) && (false !== strpos($rows, ','))) {
			$this->_readRows = explode(',', $rows);
			// extra plus because of first heading row
			foreach ($this->_readRows as &$row) {
				$row++;
			}			
			// for O(1) access
			$this->_readRows = array_flip($this->_readRows);
		}
	}
    
	/**
	 * 
	 * Load first row from document so called headers
	 * 
	 * @return array
	 */
	public function getDocumentColumnNames()
	{
		$this->_initFileSpecs();
		
		$columns = array();
		
		$fh = fopen($this->_fileName, 'r');
		$columns = fgetcsv($fh, $this->_lineLength, $this->_delimiter, $this->_enclosure);
		fclose($fh);
		
		return $columns;
	}
    
    /**
     * Retrieve CSV file data as pairs
     *
     * @param   string $file
     * @param   int $keyIndex
     * @param   int $valueIndex
     * @return  array
     */
    public function getDataPairs($file, $keyIndex=0, $valueIndex=1)
    {
        $data = array();
        $csvData = $this->getData($file);
        foreach ($csvData as $rowData) {
        	if (isset($rowData[$keyIndex])) {
        	    $data[$rowData[$keyIndex]] = isset($rowData[$valueIndex]) ? $rowData[$valueIndex] : null;
        	}
        }
        return $data;
    }
    
    /**
     * Saving data row array into file
     *
     * @param   string $file
     * @param   array $data
     * @return  Gp_File_Csv
     */
    public function saveData($file, $data)
    {
        $fh = fopen($file, 'w');
        foreach ($data as $dataRow) {
            $this->fputcsv($fh, $dataRow, $this->_delimiter, $this->_enclosure);
        }
        fclose($fh);
        return $this;
    }
    
    public function fputcsv(&$handle, $fields = array(), $delimiter = ',', $enclosure = '"') {
        $str = '';
        $escape_char = '\\';
        foreach ($fields as $value) {
            if (strpos($value, $delimiter) !== false ||
                strpos($value, $enclosure) !== false ||
                strpos($value, "\n") !== false ||
                strpos($value, "\r") !== false ||
                strpos($value, "\t") !== false ||
                strpos($value, ' ') !== false) {
                $str2 = $enclosure;
                $escaped = 0;
                $len = strlen($value);
                for ($i=0;$i<$len;$i++) {
                    if ($value[$i] == $escape_char) {
                        $escaped = 1;
                    } else if (!$escaped && $value[$i] == $enclosure) {
                        $str2 .= $enclosure;
                    } else {
                        $escaped = 0;
                    }
                        $str2 .= $value[$i];
                }
                $str2 .= $enclosure;
                $str .= $str2.$delimiter;
            } else {
                $str .= $enclosure.$value.$enclosure.$delimiter;
            }
        }
        $str = substr($str,0,-1);
        $str .= "\n";
        return fwrite($handle, $str);
    }
    
	/**
	 * 
	 * Get file name and type
	 */
	protected function _initFileSpecs()
	{
		if (empty($this->_fileInstance)) {
			throw new Exception('No file selected');
		}
		
		$this->_fileName = self::BASIC_EXCEL_UPLOAD_PATH . DS . $this->_fileInstance->name;
		
		// check whether file exists
		if (!file_exists($this->_fileName)) {
			throw new Exception('File "' . $this->_fileName . '" do not exists');
		}
		
		return $this;
	}
}